﻿using System;
using System.Collections.Generic;
using MySql.Data.MySqlClient;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using Titan.ExpressionAnalyse;
using System.Text.RegularExpressions;
using System.ComponentModel;
using System.Diagnostics;
using System.Collections;

namespace Titan.MySql
{
    public class MySqlSqlProvider : SqlProvider
    {
        private const string PARAMETER_PREFIX = "?";

 

        public override IDbConnection CreateConnection()
        {
            return new MySqlConnection();
        }

        public override DbDataAdapter CreateDataAdapter()
        {
            return new MySqlDataAdapter();
        }


        public override void WrapPaging(IDbSession session, IDbCommand command, int pageSize, int pageIndex)
        {
            MySqlCommand mysqlCommand = (MySqlCommand)command;
            Util.CheckPageSizeAndPageIndex(pageSize, pageIndex);


            StringBuilder sb = new StringBuilder();
            sb.Append(command.CommandText);
            sb.Append(" limit ");
            sb.Append(PARAMETER_PREFIX);
            sb.Append("PagerPageSize");
            sb.Append(" offset ");
            sb.Append(PARAMETER_PREFIX);
            sb.Append("PagerOffset");



            int pagerOffset = (pageIndex - 1) * pageSize;
            global::MySql.Data.MySqlClient.MySqlParameter pPageSize = new global::MySql.Data.MySqlClient.MySqlParameter(PARAMETER_PREFIX + "PagerPageSize", DbType.Int32);
            pPageSize.Value = pageSize;
            mysqlCommand.Parameters.Add(pPageSize);
            global::MySql.Data.MySqlClient.MySqlParameter pPageOffset = new global::MySql.Data.MySqlClient.MySqlParameter(PARAMETER_PREFIX + "PagerOffset", DbType.Int32);
            pPageOffset.Value = pagerOffset;
            mysqlCommand.Parameters.Add(pPageOffset);

            mysqlCommand.CommandText = sb.ToString(); 
        }

        public override object ConvertDbValue(object value, Type expectedType)
        {
            if (value == null || value == DBNull.Value) return null;
            if (expectedType.IsGenericType && expectedType.GetGenericTypeDefinition() == typeof(Nullable<>))
            {
                Type innerType = Nullable.GetUnderlyingType(expectedType);
                return ConvertDbValue(value, innerType);
            }
            if (expectedType.IsEnum)
            {
                Type innerType = Enum.GetUnderlyingType(expectedType);
                return Enum.ToObject(expectedType, ConvertDbValue(value, innerType));
            }
            
            return Convert.ChangeType(value, expectedType);
        }
        protected override IDataParameter CreateParameter(string parameterName, object parameterValue, Type propertyType,int length)
        {
            var parameter = new global::MySql.Data.MySqlClient.MySqlParameter { ParameterName = parameterName };
            parameter.MySqlDbType = GetMySqlDbType(propertyType);
            if (length > 0 && (parameter.MySqlDbType == MySqlDbType.VarChar || parameter.MySqlDbType == MySqlDbType.VarString))
            {
                parameter.Size = length;
            }
            if (parameterValue == null)
            { 
                parameter.Value = DBNull.Value;
            }
            else
            {
                if (parameterValue is bool)
                {
                    parameter.Value = (bool)parameterValue ? 1 : 0;
                }
                else if (parameterValue is Enum)
                {
                    parameter.Value = (int)parameterValue;
                }
                else
                {
                    parameter.Value = parameterValue;
                }
            }
            return parameter;
        }


        private static readonly Dictionary<Type, MySqlDbType> CachedSqlServerDbTypes = new Dictionary<Type, MySqlDbType>() { { typeof(byte[]), MySqlDbType.Blob } };
        /// <summary>
        /// 根据类型获取存储过程参数类型
        /// </summary>
        /// <param name="parameterValueType"></param>
        /// <returns></returns>
        private static MySqlDbType GetMySqlDbType(Type parameterValueType)
        {
            if (!CachedSqlServerDbTypes.ContainsKey(parameterValueType))
            {
                lock (CachedSqlServerDbTypes)
                {
                    if (!CachedSqlServerDbTypes.ContainsKey(parameterValueType))
                    {
                        Type innerType = parameterValueType;
                        if (innerType.IsGenericType && innerType.GetGenericTypeDefinition() == typeof(Nullable<>))
                        {
                            innerType = Nullable.GetUnderlyingType(innerType);
                        }
                        //nullable中可能还有枚举
                        if (innerType.IsEnum)
                        {
                            innerType = Enum.GetUnderlyingType(innerType);
                        }

                        MySqlDbType sqlDbType = MySqlDbType.VarString;
                        global::MySql.Data.MySqlClient.MySqlParameter param = new global::MySql.Data.MySqlClient.MySqlParameter();
                        TypeConverter tc = TypeDescriptor.GetConverter(param.DbType);
                        if (tc.CanConvertFrom(innerType))
                        {
                            param.DbType = (DbType)tc.ConvertFrom(innerType.Name);
                        }
                        else
                        {
                            try
                            {
                                param.DbType = (DbType)tc.ConvertFrom(innerType.Name);
                            }
                            catch (Exception ex)
                            {
                                throw ex;
                            }
                        }
                        sqlDbType = param.MySqlDbType;

                        CachedSqlServerDbTypes.Add(parameterValueType, sqlDbType);
                    }
                }
            }
            return CachedSqlServerDbTypes[parameterValueType];
        } 

        #region 对象操作

        public override int Insert(IDbSession session, IDictionary<string, string> tableMapping, EntityType entityType, INameValueExpressionCollection nameValues, object obj)
        { 
            var table = GetTableByEntityType(entityType);


            var sb = new StringBuilder();
            var command = new MySqlCommand();
            var parameterCounter = new Counter(1);

            #region 循环每个属性

            var tableName = GetTableName(entityType, tableMapping);
            sb.Append("insert into ");
            sb.Append(tableName);
            sb.Append(" (");
            //Oracle存储过程参数有顺序之分
            //List<OracleParameter> returnParameters = new List<OracleParameter>();//取回的存储过程
            var returnColumns = new Dictionary<string, IColumn>(StringComparer.OrdinalIgnoreCase);

            int index = 0;
            foreach (NameValueExpression nameValue in nameValues)
            {
                if (index > 0) { sb.Append(","); }

                var column = table.Columns[nameValue.PropertyName];
                if (column.InsertBehavior.Generate)
                {
                    sb.Append(column.ColumnName);
                    index++;
                }
            }
            sb.Append(") values (");


            index = 0;
            foreach (NameValueExpression nameValue in nameValues)
            {
                if (index > 0) { sb.Append(","); }
                IColumn column = table.Columns[nameValue.PropertyName];
                if (column.InsertBehavior.Generate)
                {
                    if (column.InsertBehavior.ValueBehavior == ValueBehavior.UseValueExpression)
                    {
                        sb.Append(column.InsertBehavior.ValueExpression);
                    }
                    else
                    {
                        sb.Append(PARAMETER_PREFIX);
                        sb.Append(parameterCounter.CurrentValue);

                         
                        //object propertyValue = column.PropertyAdapter.GetValue(obj);
                        //Validate(column, propertyValue);//验证如：检查字符串长度，是否为null
                        AddParameter(command.Parameters, parameterCounter, PARAMETER_PREFIX, nameValue.PropertyValue, column.PropertyAdapter.PropertyType.Type, column.Size);

                    }
                    index++;
                }
            }
            sb.Append(")");

            foreach (IdentityColumn column in table.PrimaryKeyColumns)
            {
                //取回主键值 ，本版本只支持主键值的取回
                if (column.IsIdentity && column.ReturnAfterInsert)
                {
                    returnColumns.Add(PARAMETER_PREFIX + parameterCounter.CurrentValue, column);

                    sb.Append(";select LAST_INSERT_ID();");
                    break;//只能有一个
                }
            }
            #endregion


            command.CommandText = sb.ToString();
            command.CommandType = CommandType.Text;


            //执行sql
            object returnValue = session.ExecuteScalar(command);

            //read return value  
            foreach (KeyValuePair<string, IColumn> item in returnColumns)
            {
                item.Value.PropertyAdapter.SetValue(obj, ConvertDbValue(returnValue, item.Value.PropertyAdapter.PropertyType.Type));
                break;//只能有一个
            } 

            return 1;
        }

        public override int SelectCollection(IDbSession session, IDictionary<string, string> tableMapping, EntityType entityType, IList list, IQueryExpression queryExpression, bool clearListBeforeFill)
        {
            //检查pageSize和pageIndex是否合法
            if (queryExpression.PageIndex.HasValue && queryExpression.PageSize.HasValue)
            {
                Util.CheckPageSizeAndPageIndex(queryExpression.PageSize.Value, queryExpression.PageIndex.Value);
            }

            SqlAnalyseResult sqlAnalyseResult = SqlAnalyzer.Analyse(this, entityType, queryExpression);


            var sb = new StringBuilder();
            var cmd = session.Connection.CreateCommand();
            var counter = new Counter(1);


            GenerateSelectSql_SelectFromWhereOrder(sb, cmd.Parameters, counter, PARAMETER_PREFIX, PARAMETER_PREFIX, queryExpression, sqlAnalyseResult, tableMapping);


            //执行sql
            cmd.CommandText = sb.ToString();
            cmd.CommandType = CommandType.Text;
            if (queryExpression.PageIndex != null && queryExpression.PageSize != null)
            {
                //分页的时候需要包装分页语句
                WrapPaging(session,cmd, queryExpression.PageSize.Value, queryExpression.PageIndex.Value);
            }

            IDataReader dataReader = session.ExecuteReader(cmd);

            if (clearListBeforeFill)
            {
                list.Clear();
            }

            int readedcount = 0;
            while (dataReader.Read())
            {
                if (list.Count <= readedcount)
                {
                    list.Add(Activator.CreateInstance(entityType.Type));
                }

                object obj = list[readedcount];
                sqlAnalyseResult.ObjectFiller.Fill(this, obj, dataReader);
                readedcount++;
            }
            dataReader.Close();
            int returnCount = readedcount;//注意此处必须返回实际读到的个数
            if (queryExpression.ReturnMatchedCount)
            {
                if (sqlAnalyseResult.HasGroup)
                {
                    returnCount = SelectCountGroup(session, tableMapping, entityType, queryExpression, sqlAnalyseResult);
                }
                else
                {
                    returnCount = SelectCount(session, tableMapping, entityType, queryExpression.Wheres);
                }
            }
            return returnCount;
        }
        public int SelectCountGroup(IDbSession session, IDictionary<string, string> tableMapping, EntityType entityType, IQueryExpression queryExpression, SqlAnalyseResult sqlAnalyseResult)
        {
            StringBuilder sb = new StringBuilder();
            IDbCommand command = session.Connection.CreateCommand();
            Counter parameterCounter = new Counter(1);

            //创建一个QueryExpression 


            sb.Append("select count(0) from (");
            //select
            sb.Append("select ");
            foreach (OutputSqlColumn outputSqlColumn in sqlAnalyseResult.SortedOutputSqlColumns)
            {
                sb.Append(outputSqlColumn.TableAliasAndColumnAlias);
                break;//只需要输出一个
            }


            //from where
            GenerateSelectSql_FromWhere(sb, command.Parameters, parameterCounter, PARAMETER_PREFIX, PARAMETER_PREFIX, queryExpression, sqlAnalyseResult, tableMapping);

            sb.Append(") a");








            //执行sql
            command.CommandText = sb.ToString();
            command.CommandType = CommandType.Text;
            IDataReader dataReader = session.ExecuteReader(command);


            int count = 0;
            if (dataReader.Read())
            {
                count = Convert.ToInt32(dataReader[0]);
            }
            dataReader.Close();
            return count;
        }
        public override int SelectCount(IDbSession session, IDictionary<string, string> tableMapping, EntityType entityType, IConditionCollection conditionCollection)
        {


            var sb = new StringBuilder();
            var command = session.Connection.CreateCommand();
            var parameterCounter = new Counter(1);

            //创建一个QueryExpression
            IQueryExpression queryExpression = new QueryExpression ();
            queryExpression.Wheres = conditionCollection;
            var sqlAnalyseResult = SqlAnalyzer.Analyse(this, entityType, queryExpression);



            sb.Append("select count(0) ");
            GenerateSelectSql_FromWhere(sb, command.Parameters, parameterCounter, PARAMETER_PREFIX, PARAMETER_PREFIX, queryExpression, sqlAnalyseResult, tableMapping);







            //执行sql
            command.CommandText = sb.ToString();
            command.CommandType = CommandType.Text;
            IDataReader dataReader = session.ExecuteReader(command);


            int count = 0;
            if (dataReader.Read())
            {
                count = Convert.ToInt32(dataReader[0]);
            }
            dataReader.Close();
            return count;
        }

        public override bool Exists(IDbSession session, IDictionary<string, string> tableMapping, EntityType entityType, IConditionCollection conditionCollection)
        {
            IQueryExpression queryExpression = new QueryExpression();
            queryExpression.Wheres = conditionCollection;
            var sqlAnalyseResult = SqlAnalyzer.Analyse(this, entityType, queryExpression);


            var sb = new StringBuilder();
            var command = session.Connection.CreateCommand();
            var parameterCounter = new Counter(1);



            sb.Append("select 0 ");
            GenerateSelectSql_FromWhere(sb, command.Parameters, parameterCounter, PARAMETER_PREFIX, PARAMETER_PREFIX, queryExpression, sqlAnalyseResult, tableMapping);
            sb.Append(" limit 1");

            //sb.Append(conditionCollection.Count > 0 ? " and " : " where ");
            //sb.Append(" rownum<2");




            //执行sql
            command.CommandText = sb.ToString();
            command.CommandType = CommandType.Text;
            IDataReader dataReader = session.ExecuteReader(command);


            bool exists = dataReader.Read();
            dataReader.Close();
            return exists;
        }

        public override int Delete(IDbSession session, IDictionary<string, string> tableMapping, EntityType entityType, IConditionCollection conditionCollection)
        {
            //ITable table=getTables().getTableByClass(clazz);

            IQueryExpression queryExpression = new QueryExpression();
            queryExpression.Wheres = conditionCollection;
            SqlAnalyseResult sqlAnalyseResult = SqlAnalyzer.Analyse(this, entityType, queryExpression);



            //执行关联删除 
            var sb = new StringBuilder();
            var command = session.Connection.CreateCommand();
            var parameterCounter = new Counter(1);

            sb.Append("delete ");
            sb.Append(sqlAnalyseResult.MasterTableNameAlias);
            GenerateSelectSql_FromWhere(sb, command.Parameters, parameterCounter, PARAMETER_PREFIX, PARAMETER_PREFIX, queryExpression, sqlAnalyseResult, tableMapping); 

            //执行sql
            command.CommandText = sb.ToString();
            command.CommandType = CommandType.Text;
            int returnCount = session.ExecuteNonQuery(command);


            return returnCount;
        }

        public override int Update(IDbSession session, IDictionary<string, string> tableMapping, EntityType entityType, IUpdateExpression updateExpression)
        { 
            var table = GetTableByEntityType(entityType); 
            SqlAnalyseResult sqlAnalyseResult = SqlAnalyzer.Analyse(this, entityType, updateExpression);



            var sb = new StringBuilder();
            var command = session.Connection.CreateCommand();
            var parameterCounter = new Counter(1);


            int index = 0;

            sb.Append("update "); 
            sb.Append(GetTableName(sqlAnalyseResult.MasterEntityType, tableMapping));
            sb.Append(" ");
            sb.Append(sqlAnalyseResult.MasterTableNameAlias);



            //join
            index = 0;
            foreach (SqlTable sqlTable in sqlAnalyseResult.ForeignTables)
            {
                sb.Append(" left outer join ");
                sb.Append(GetTableName(sqlTable.ForeignEntityType, tableMapping));
                sb.Append(" ");
                sb.Append(sqlTable.ForeignTableNameAlias);

                sb.Append(" on ");
                index = 0;
                foreach (SqlRelationColumn sqlRelationColumn in sqlTable.RelationColumns)
                {
                    if (index > 0) { sb.Append(" and "); }
                    index++;
                    sb.Append(sqlRelationColumn.Expression);

                }
            }

            sb.Append(" set ");
            index = 0;
            foreach (NameValueExpression nameValue in updateExpression.Updates)
            {
                if (index > 0) { sb.Append(","); }
                index++;
                IColumn column = table.Columns[nameValue.PropertyName];
                sb.Append(sqlAnalyseResult.MasterTableNameAlias);
                sb.Append(".");
                sb.Append(column.ColumnName);
                sb.Append("=");
                sb.Append(PARAMETER_PREFIX);
                sb.Append(parameterCounter.CurrentValue);


                //object propertyValue = column.PropertyAdapter.GetValue(obj);
                //Validate(column, propertyValue);//验证如：检查字符串长度，是否为null
                AddParameter(command.Parameters, parameterCounter, PARAMETER_PREFIX, nameValue.PropertyValue, column.PropertyAdapter.PropertyType.Type, column.Size);

            }

            //where 
            if (updateExpression.Wheres.Count > 0)
            {
                sb.Append(" where ");
                GenerateSelectSql_Where(sb, command.Parameters, parameterCounter, PARAMETER_PREFIX, PARAMETER_PREFIX, updateExpression.Wheres, sqlAnalyseResult, true);
            }

             

            //执行sql
            command.CommandText = sb.ToString();
            command.CommandType = CommandType.Text;
            int returnCount = session.ExecuteNonQuery(command);


            return returnCount;
        }
        
        #endregion       
        
        //#region 对象操作

        //public override int Insert(IDbSession session, ITableMapping tableMapping, object obj)
        //{
        //    var entityType = obj.GetType();
        //    var table = GetTableByEntityType(entityType);


        //    var sb = new StringBuilder();
        //    var command = new SqlCommand();
        //    var parameterCounter = new Counter(1);

        //    #region 循环每个属性
        //    sb.Append("begin insert into ");
        //    sb.Append(GetTableName(entityType, tableMapping));
        //    sb.Append(" (");
            
        //    var returnColumns = new Dictionary<string, SqlServerColumn>();

        //    var index = 0;
        //    foreach (SqlServerColumn column in table.InsertColumns)
        //    {
        //        if (index > 0) { sb.Append(","); }
        //        index++;
        //        sb.Append(column.ColumnName);
        //    }
        //    sb.Append(") values (");

        //    index = 0;
        //    foreach (SqlServerColumn column in table.InsertColumns)
        //    {
        //        if (index > 0) { sb.Append(","); }
        //        index++;
                
        //            sb.Append(ParameterPrefixInStatement);
        //            sb.Append(parameterCounter.CurrentValue);
        //            //检查字符串长度，是否为null
        //            //CheckPropertyValue(c, isNull, objValue); 
        //            var propertyValue = column.PropertyAdapter.GetValue(obj);
        //            AddParameter(command.Parameters, parameterCounter, ParameterPrefix, propertyValue, column.PropertyAdapter.PropertyType);
                
        //    }
        //    sb.Append(")");

        //    //"INSERT INTO SequenceTest_Table (ID, OtherColumn)" +
        //    //"VALUES (SequenceTest_Sequence.NEXTVAL, :OtherColumn)" +
        //    //"RETURNING ID INTO :ID";
            
        //    sb.Append("; end;");
        //    #endregion

        //    command.CommandText = sb.ToString();
        //    command.CommandType = CommandType.Text;

        //    //执行sql
        //    var returnCount = session.ExecuteNonQuery(command);

        //    //read return value  
        //    foreach (var item in returnColumns)
        //    {
        //        var objValue = command.Parameters[item.Key].Value;  //GetCommandValue(cmd, pName);
        //        item.Value.PropertyAdapter.SetValue(obj, objValue);
        //    }

        //    return returnCount;
        //}

        //public override int SelectCollection(IDbSession session, ITableMapping tableMapping, System.Collections.IList list, bool clearListBeforeFill, Type entityType, IQueryExpression queryExpression)
        //{
        //    //if (queryExpression.PageIndex < 0 || queryExpression.PageSize < 0)
        //    //{
        //    //    throw new TitanException("要求非负数，两个参数都为0表示取回全部，要么两个参数都大于0,pageIndex从1开始");
        //    //}
        //    //if (queryExpression.PageSize > 0 && queryExpression.PageIndex == 0)
        //    //{
        //    //    throw new TitanException("pageSize>0时，pageIndex必须从1开始");
        //    //} 
        //    var sqlAnalyseResult = SqlAnalyzer.Analyse(this, entityType, queryExpression);

        //    var sb = new StringBuilder();
        //    var command = session.Connection.CreateCommand();
        //    var parameterCounter = new Counter(1);

        //    if (queryExpression.PageIndex == null || queryExpression.PageSize == null)
        //    {
        //        GenerateSelectSql_SelectFromWhereOrder(sb, command.Parameters, parameterCounter, ParameterPrefix, ParameterPrefixInStatement, queryExpression, sqlAnalyseResult, tableMapping);
        //    }
        //    else
        //    {
        //        var upperBound = queryExpression.PageSize.Value * queryExpression.PageIndex.Value + 1;
        //        var lowerBound = (queryExpression.PageIndex.Value - 1) * queryExpression.PageSize.Value;

        //        sb.Append("select * from ( select ta.*, rownum rn from ( ");
        //        GenerateSelectSql_SelectFromWhereOrder(sb, command.Parameters, parameterCounter, ParameterPrefix, ParameterPrefixInStatement, queryExpression, sqlAnalyseResult, tableMapping);
        //        sb.Append(") ta where rownum <");
        //        sb.Append(ParameterPrefixInStatement);
        //        sb.Append(parameterCounter.CurrentValue);

        //        AddParameter(command.Parameters, parameterCounter, ParameterPrefix, upperBound, typeof(int)); 

        //        sb.Append(") tb where rn > ");
        //        sb.Append(ParameterPrefixInStatement);
        //        sb.Append(parameterCounter.CurrentValue);
        //        sb.Append(" ");



        //        AddParameter(command.Parameters, parameterCounter, ParameterPrefix, lowerBound, typeof(int));  

        //    }


        //    //执行sql
        //    command.CommandText = sb.ToString();
        //    command.CommandType = CommandType.Text;

        //    var dataReader = session.ExecuteReader(command);

        //    if (clearListBeforeFill)
        //    {
        //        list.Clear();
        //    }

        //    var readcount = 0;
        //    while (dataReader.Read())
        //    {
        //        if (list.Count <= readcount)
        //        { 
        //            list.Add(Activator.CreateInstance(entityType));
        //        }
                
        //        var obj =list[readcount];
        //        sqlAnalyseResult.ObjectFiller.Fill(this,obj, dataReader);
        //        readcount++;
        //    }
        //    dataReader.Close();
        //    var returnCount = 0;
        //    if (queryExpression.ReturnMatchedCount)
        //    {
        //        returnCount = SelectCount(session, tableMapping, entityType, queryExpression.Wheres);
        //    }
        //    return returnCount;
        //}

        //public override int SelectCount(IDbSession session, ITableMapping tableMapping, Type entityType, IConditionCollection conditionCollection)
        //{
        //    var sb = new StringBuilder();
        //    var command = session.Connection.CreateCommand();
        //    var parameterCounter = new Counter(1);

        //    //创建一个QueryExpression
        //    var queryExpression = new QueryExpression();
        //    queryExpression.Wheres = conditionCollection;
        //    var sqlAnalyseResult = SqlAnalyzer.Analyse(this, entityType, queryExpression);

        //    sb.Append("select count(0) ");
        //    GenerateSelectSql_FromWhere(sb, command.Parameters, parameterCounter, ParameterPrefix, ParameterPrefixInStatement, queryExpression, sqlAnalyseResult, tableMapping);

        //    //执行sql
        //    command.CommandText = sb.ToString();
        //    command.CommandType = CommandType.Text;
        //    var dataReader = session.ExecuteReader(command);


        //    var count = 0;
        //    if (dataReader.Read())
        //    {
        //        count = Convert.ToInt32(dataReader[0]);
        //    }
        //    dataReader.Close();
        //    return count;
        //}

        //public override bool Exists(IDbSession session, ITableMapping tableMapping, Type entityType, IConditionCollection conditionCollection)
        //{
        //    var queryExpression = new QueryExpression {Wheres = conditionCollection};
        //    var sqlAnalyseResult = SqlAnalyzer.Analyse(this, entityType, queryExpression);

        //    var sb = new StringBuilder();
        //    var command = session.Connection.CreateCommand();
        //    var parameterCounter = new Counter(1);

        //    sb.Append("select 0 ");
        //    GenerateSelectSql_FromWhere(sb, command.Parameters, parameterCounter, ParameterPrefix, ParameterPrefixInStatement, queryExpression, sqlAnalyseResult, tableMapping);

        //    sb.Append(conditionCollection.Count > 0 ? " and " : " where ");
        //    sb.Append(" rownum<2");

        //    //执行sql
        //    command.CommandText = sb.ToString();
        //    command.CommandType = CommandType.Text;
        //    var dataReader = session.ExecuteReader(command);

        //    var exists = dataReader.Read();
        //    dataReader.Close();
        //    return exists;
        //}

        //public override int BatchDelete(IDbSession session, ITableMapping tableMapping, Type entityType, IConditionCollection conditionCollection)
        //{
        //    //ITable table=getTables().getTableByClass(clazz);
        //    var queryExpression = new QueryExpression {Wheres = conditionCollection};
        //    var sqlAnalyseResult = SqlAnalyzer.Analyse(this, entityType, queryExpression);

        //    //执行关联删除 
        //    var sb = new StringBuilder();
        //    var command = session.Connection.CreateCommand();
        //    var parameterCounter = new Counter(1);

        //    sb.Append("delete  ");
        //    sb.Append(sqlAnalyseResult.MasterTableNameAlias);
        //    sb.Append(" from ");
        //    sb.Append(GetTableName(entityType, tableMapping));
        //    sb.Append(" ");
        //    sb.Append(sqlAnalyseResult.MasterTableNameAlias);
            
        //    SqlServerCreateSqlExists(sb, command.Parameters, parameterCounter, queryExpression, sqlAnalyseResult, tableMapping);

        //    //执行sql
        //    command.CommandText = sb.ToString();
        //    command.CommandType = CommandType.Text;
        //    return session.ExecuteNonQuery(command);
        //}

        //public override int BatchUpdate(IDbSession session, ITableMapping tableMapping, IConditionCollection conditionCollection, object obj, IPropertyExpression[] propertyExpressions)
        //{
        //    var entityType = obj.GetType();
        //    var table = GetTableByEntityType(entityType);

        //    //执行关联修改
        //    var sb = new StringBuilder();
        //    var command = session.Connection.CreateCommand();
        //    var parameterCounter = new Counter(1);

        //    var queryExpression = new QueryExpression
        //                              {
        //                                  Selects = new List<IPropertyExpression>(propertyExpressions),
        //                                  Wheres = conditionCollection
        //                              };
        //    var sqlAnalyseResult = SqlAnalyzer.Analyse(this, entityType, queryExpression);

        //    sb.Append("update ");
        //    sb.Append(sqlAnalyseResult.MasterTableNameAlias);
        //    sb.Append(" set ");
        //    var index = 0;
        //    foreach (var propertyExpression in propertyExpressions)
        //    {
        //        if (index > 0) { sb.Append(","); }
        //        index++;
        //        var column = table.Columns[propertyExpression.PropertyName];
        //        sb.Append(column.ColumnName);
        //        sb.Append("=");
        //        sb.Append(ParameterPrefixInStatement);
        //        sb.Append(parameterCounter.CurrentValue);
 
        //        var propertyValue = column.PropertyAdapter.GetValue(obj);
        //        AddParameter(command.Parameters, parameterCounter, ParameterPrefix, propertyValue, column.PropertyAdapter.PropertyType);
        //    }
        //    sb.Append(" from ");
        //    sb.Append(GetTableName(entityType, tableMapping));
        //    sb.Append(" ");
        //    sb.Append(sqlAnalyseResult.MasterTableNameAlias);
        //    SqlServerCreateSqlExists(sb, command.Parameters, parameterCounter, queryExpression, sqlAnalyseResult, tableMapping);
            

        //    //执行sql
        //    command.CommandText = sb.ToString();
        //    command.CommandType = CommandType.Text;
        //    var returnCount = session.ExecuteNonQuery(command);

        //    return returnCount;
        //}
        //#endregion

        private void SqlServerCreateSqlExists(
            StringBuilder sb, IDataParameterCollection parameters, Counter parameterCounter,
            IQueryExpression queryExpression, SqlAnalyseResult sqlAnalyseResult,
            IDictionary<string, string> tableMapping
            )
        {
            

            var tableIndex = 0;
            if (sqlAnalyseResult.ForeignTables.Count > 0)
            {
                sb.Append("select 1 from ");
                foreach (var sqlTable in sqlAnalyseResult.ForeignTables)
                {
                    if (tableIndex > 0)
                    {
                        sb.Append(",");
                    }
                    tableIndex++;
                    sb.Append(GetTableName(sqlTable.ForeignEntityType, tableMapping));
                    sb.Append(" ");
                    sb.Append(sqlTable.ForeignTableNameAlias);
                }
                tableIndex = 0;
                foreach (var sqlTable in sqlAnalyseResult.ForeignTables)
                {
                    foreach (var sqlRelationColumn in sqlTable.RelationColumns)
                    {
                        sb.Append(tableIndex > 0 ? " and " : " where ");
                        tableIndex++;

                        sb.Append(sqlRelationColumn.Expression);
                    }
                }
            }
            
            //condition
            if (queryExpression.Wheres.Count <= 0) return;
            sb.Append(tableIndex > 0 ? " and " : " where ");
            GenerateSelectSql_Where(sb, parameters, parameterCounter, PARAMETER_PREFIX, PARAMETER_PREFIX,
                                    queryExpression.Wheres, sqlAnalyseResult,true);
            //order 不需要order
        }


        protected override void GenerateSelectSql_Where_Item(StringBuilder sb, IDataParameterCollection parameters, Counter parameterCounter, string parameterNamePrefix, string parameterNamePrefixInStatement, IConditionItem conditionItem, SqlColumn sqlColumn, bool includeTableNameAlias)
        {
            if (conditionItem.ConditionOperator == ConditionOperator.FullTextLike || conditionItem.ConditionOperator == ConditionOperator.NotFullTextLike)
            {
                //sql server 全文索引总是contains
                //if (string.IsNullOrWhiteSpace(sqlColumn.Column.FullTextSearch))
                //{
                //    throw ExceptionFactory.FullTextSearchNotSupported(sqlColumn.Column.PropertyAdapter.PropertyName);
                //}
                string fulltextSearch = sqlColumn.Column.FullTextSearch.ToLower();
                if (string.IsNullOrWhiteSpace(fulltextSearch)) fulltextSearch = "contains";
                string tag = conditionItem.ConditionOperator == ConditionOperator.FullTextLike ? ">" : "<=";
                switch (fulltextSearch)
                {
                    case "contains":
                        sb.Append("contains(");
                        sb.Append(sqlColumn.TableAliasAndColumn);
                        sb.Append(",");
                        sb.Append(parameterNamePrefixInStatement + parameterCounter.CurrentValue);
                        sb.Append(",1)");
                        sb.Append(tag);
                        sb.Append("0");
                        break; 
                    default:
                        //function({column},{parameter},1)>0
                        string s = fulltextSearch.Replace("{column}", sqlColumn.TableAliasAndColumn);
                        s = s.Replace("{parameter}", parameterNamePrefixInStatement + parameterCounter.CurrentValue);
                        sb.Append(s);
                        break;
                }
                AddParameter(parameters, parameterCounter, parameterNamePrefix, conditionItem.ConditionValue, sqlColumn.Column.PropertyAdapter.PropertyType.Type, sqlColumn.Column.Size);
            }
            else
            {
                base.GenerateSelectSql_Where_Item(sb, parameters, parameterCounter, parameterNamePrefix, parameterNamePrefixInStatement, conditionItem, sqlColumn, includeTableNameAlias);
            }
        }

        protected override IAttributeReader CreateAttributeReader()
        {
            return new MySqlAttributeReader();
        }
    }
}
